package prepared;

import org.junit.Test;
import utils.JDBCutils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PreparedStatement_CRUD {
    //查
    @Test
    public  void  finAll() throws SQLException {
        Connection con  = JDBCutils.getConnection();
        //创建一个预编译对象
        String sql = "select * from user";
        PreparedStatement ps   = con.prepareStatement(sql);
        ResultSet rs  = ps.executeQuery();
        while (rs.next()){
            int id = rs.getInt(1);
            String username = rs.getString(2);
            String password =rs.getString(3);
            System.out.println(id + "\t" + username + "\t" + password);
        }

        JDBCutils.closeResources(rs,ps,con);

    }
    //增
    @Test
    public void insertUser() throws SQLException {
        Connection con  = JDBCutils.getConnection();
        String sql = "insert  into user values (?,?,?)";
        PreparedStatement ps  = con.prepareStatement(sql);
        ps.setInt(1,4);
        ps.setString(2,"王雅贤");
        ps.setString(3,"0719");
        System.out.println(ps.executeUpdate() > 0 ? "添加成功" : "添加失败");
        JDBCutils.closeResources(null,ps,con);
    }
    @Test
    public void updateUser() throws SQLException {
        Connection con  = JDBCutils.getConnection();
        String sql = "update user set id = ? where username = ?";
        PreparedStatement ps  = con.prepareStatement(sql);
        ps.setInt(1,3);
        ps.setString(2,"王雅贤");
        System.out.println(ps.executeUpdate() > 0 ? "修改成功" : "修改失败");
        JDBCutils.closeResources(null,ps,con);
    }
    @Test
    public void deleteUser() throws SQLException {
        Connection con = JDBCutils.getConnection();
        String sql = "delete from user where id = ?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1,1);
        System.out.println(ps.executeUpdate() > 0 ? "删除成功" : "删除失败");
        JDBCutils.closeResources(null,ps,con);
    }
}
